import pandas as pd
import xlwings as xw    
import os

app = xw.App(visible=True)
wb = app.books.open(r'文件路径')

# df_total = wb.sheets[0].range('A1').expand().options(index=False, header=False).value

df_total = wb.sheets[0].range('A1').options(pd.DataFrame, expand='table', index=False, number=int).value
df_student_list = []
# 读取每个文件，将文件中的数据内容进行汇总
for fname in os.listdir('文件夹路径'):
    if fname.endswith('.xlsx'):
        # df_student = pd.read_excel(fname, sheet_name=None, dtype=str)
        # df_student_list.append(df_student.values())
        wb = app.books.open(r'文件夹路径' + fname)
        df_student = wb.sheets[0].range('A1').options(pd.DataFrame, expand='table', index=False, number=int).value
        df_student['班级'] = '班级名称'
        
        df_student_list.append(df_student)
        wb.close()

df_student_all = pd.concat(df_student_list)

# 合并数据
df_merge = pd.merge(left=df_total, right=df_student_all, left_on=['班级','姓名'], right_on=['班级','姓名'], how='left')

